﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DataAccess
{
    public class CategoryDAL
    {
        private static readonly string SQL_GETALL = "select * from groups";

        public static List<Category> GetAll()
        {
            List<Category> list = new List<Category>();
            var command = SqlHelper.GetCommand(SQL_GETALL);
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(Build(reader));
            }
            return list;
        }

        protected static Category Build(DbDataReader reader)
        {
            Category category = new Category();

            if (!(reader["group_create_date"] is DBNull))
            {
                category.CreateDate = new DateTime((long)reader["group_create_date"]);
            }
            category.Desc = SqlHelper.GetNullableString(reader["group_desc"]);
            category.GroupIcon = SqlHelper.GetNullableString(reader["group_icon"]);
            category.GroupIndex = SqlHelper.GetNullableValue<long>(reader["group_index"]);
            category.ID = (long)reader["_id"];
            if (!(reader["is_visible"] is DBNull))
            {
                category.IsVisble = (long)reader["_id"] > 0;
            }
            category.LastModifiedTime = SqlHelper.GetDateTimeFromNullableLong(reader["last_modified_time"]);
            category.LastSyncTimeSpan = SqlHelper.GetTimeSpanFromNullableLong(reader["sync_timestamp"]);

            category.Name = SqlHelper.GetNullableString(reader["group_name"]);
            category.Password = SqlHelper.GetNullableString(reader["group_pwd"]);
            if (!(reader["should_sync"] is DBNull))
            {
                category.ShouldSync = (long)reader["should_sync"] > 0;
            }
            category.SyncAccountID = SqlHelper.GetNullableValue<long>(reader["sync_account_id"]);
            category.SyncExtraState = SqlHelper.GetNullableValue<long>(reader["sync_extra_state"]);
            category.SyncGuid = SqlHelper.GetNullableString(reader["sync_gid"]);
            category.SyncState = SqlHelper.GetNullableValue<long>(reader["sync_state"]);
            category.SyncVersion = SqlHelper.GetNullableValue<long>(reader["sync_version"]);
            return category;
        }
    }

    public class CategorySumViewDAL : CategoryDAL
    {
        private static readonly string SQL_GETALL = "select a.*,(select count(*) from relationship b where b.[group_id]=a.[_id]) as Count from groups a ";

        public new static List<CategorySumView> GetAll()
        {
            CategorySumView category = null;
            List<CategorySumView> list = new List<CategorySumView>();
            var command = SqlHelper.GetCommand(SQL_GETALL);
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                category = Build(reader);
                category.ChildCount = (long)reader["Count"];
                list.Add(category);
            }
            return list;
        }
    }
}
